﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.SqlTypes;
using DevExpress.XtraGrid.Views.Grid;

namespace Print_UI
{
    public partial class FrmImportScoreExcel : CommonForm
    {
        #region Constructor
        public FrmImportScoreExcel()
        {
            InitializeComponent();

        }
        #endregion
        #region Declaretion
        string sFilePath = "";
        DataTable tbdataexcel = new DataTable();
        DataTable tbstudent = new DataTable();
        DataTable tbClass = new DataTable();
        DataTable tbsubject = new DataTable();
        DataTable tbScore = new DataTable();
        DataTable tbStudentInSemster = new DataTable();
        DataTable tbStudentTab = new DataTable();
        DataTable tbClassTab = new DataTable();
        DataTable tbSubjectTab = new DataTable();
        public int iDepart, iYearschool, iSemesterID;
        private SqlConnection _cnn;
        public SqlConnection Cnn
        {
            get { return _cnn; }
            set { _cnn = value; }
        }
        private int _id = -1;
        public int Id
        {
            get { return _id; }
            set { _id = value; }
        }
        #endregion
        private void FrmImportScoreExcel_Load(object sender, EventArgs e)
        {
            try
            {

                //create column for datatable contain data of excel file.
                tbdataexcel.Columns.Add("STT", typeof(string));
                tbdataexcel.Columns.Add("StudentCode", typeof(string));
                tbdataexcel.Columns.Add("IDStudent", typeof(int));
                tbdataexcel.Columns.Add("IDStudentInSubject", typeof(int));
                tbdataexcel.Columns.Add("IDClass", typeof(int));
                tbdataexcel.Columns.Add("IDSubject", typeof(int));
                tbdataexcel.Columns.Add("Fistname", typeof(string));
                tbdataexcel.Columns.Add("LastName", typeof(string));
                tbdataexcel.Columns.Add("DayOfbird", typeof(string));
                tbdataexcel.Columns.Add("Class", typeof(string));
                tbdataexcel.Columns.Add("Subjectcode", typeof(string));
                tbdataexcel.Columns.Add("SubjectName", typeof(string));
                tbdataexcel.Columns.Add("attendance", typeof(string));
                tbdataexcel.Columns.Add("midterm", typeof(string));
                tbdataexcel.Columns.Add("FistScore", typeof(string));
                tbdataexcel.Columns.Add("SecondScore", typeof(string));
                tbdataexcel.Columns.Add("FinalScore", typeof(string));
                tbdataexcel.Columns.Add("Perofattendance", typeof(string));
                tbdataexcel.Columns.Add("PerofMidterm", typeof(string));
                tbdataexcel.Columns.Add("PerOFFinal", typeof(string));
                tbdataexcel.Columns.Add("Check", typeof(bool));
                tbdataexcel.Columns.Add("Note", typeof(string));
                //Create columns for student datatable.
                tbStudentTab.Columns.Add("StudentCode", typeof(string));
                tbStudentTab.Columns.Add("ID", typeof(int));
                tbStudentTab.Columns.Add("ClassID", typeof(int));
                tbStudentTab.Columns.Add("SectorID", typeof(int));
                tbStudentTab.Columns.Add("ClassCode", typeof(string));
                tbStudentTab.Columns.Add("Fistname", typeof(string));
                tbStudentTab.Columns.Add("LastName", typeof(string));
                tbStudentTab.Columns.Add("Check", typeof(bool));
                tbStudentTab.Columns.Add("Note", typeof(string));
                //Create columns for Class datatable.
                tbClassTab.Columns.Add("Class", typeof(string));
                tbClassTab.Columns.Add("ID", typeof(int));
                tbClassTab.Columns.Add("SectorID", typeof(int));
                tbClassTab.Columns.Add("Check", typeof(bool));
                tbClassTab.Columns.Add("Note", typeof(string));
                //Create columns for subject datatable.
                tbSubjectTab.Columns.Add("Subjectcode", typeof(string));
                tbSubjectTab.Columns.Add("ID", typeof(int));
                tbSubjectTab.Columns.Add("SubjectName", typeof(string));
                tbSubjectTab.Columns.Add("Check", typeof(bool));
                tbSubjectTab.Columns.Add("Note", typeof(string));

                // GetDataFromBD(); get data from Student table follow Department conditions
                Cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString);
                SqlDataAdapter DaStudent = new SqlDataAdapter();
                DaStudent.SelectCommand = new SqlCommand("SELECT PRI_Student.Code as StudentCode,PRI_Student.ID from PRI_Student where DepartmentID = @DepartmentID  ", Cnn);
                DaStudent.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = iDepart;
                DaStudent.Fill(tbstudent);
                //get data from Subject table follow.
                SqlCommand cmm1 = new SqlCommand("SELECT PRI_Subjects.ID , PRI_Subjects.Code from PRI_Subjects   ", Cnn);
                SqlDataAdapter ad1 = new SqlDataAdapter(cmm1);
                ad1.Fill(tbsubject);
                //get data from Class table 
                SqlDataAdapter DaClass = new SqlDataAdapter();
                DaClass.SelectCommand = new SqlCommand("SELECT CAT_Class.ID , CAT_Class.Code, SectorID from CAT_Class where DepartmentID = @DepartmentID ", Cnn);
                DaClass.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = iDepart;
                DaClass.Fill(tbClass);
                // get data from Student table follow Department,Semesters,Year conditions
                SqlDataAdapter DaScore = new SqlDataAdapter();
                DaScore.SelectCommand = new SqlCommand("select PRI_StudentInSubject.ID , PRI_StudentInSubject.StudentID ,PRI_StudentInSubject.ClassID,PRI_StudentInSubject.SubjectID,PRI_StudentInSubject.AttendanceScore,PRI_StudentInSubject.TotalScore,PRI_StudentInSubject.FinalExamScore1,PRI_StudentInSubject.FinalExcamScore2,PRI_StudentInSubject.MidtermScore from PRI_StudentInSubject where DepartmentID=@DepartmentID and YearID = @YearID and SemesterID=@SemesterID", Cnn);
                DaScore.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = iDepart;
                DaScore.SelectCommand.Parameters.Add("@SemesterID", SqlDbType.Int).Value = iSemesterID;
                DaScore.SelectCommand.Parameters.Add("@YearID", SqlDbType.Int).Value = iYearschool;
                DaScore.Fill(tbScore);

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        private void btnChooseFile_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                // browsing file
                OpenFileDialog opf = new OpenFileDialog();
                if (opf.ShowDialog() == DialogResult.OK)
                {
                    sFilePath = opf.FileName;
                    HSSFWorkbook workbook = new HSSFWorkbook((new FileStream(sFilePath, FileMode.Open, FileAccess.Read)));
                    ISheet sheet = workbook.GetSheetAt(0);
                    tbdataexcel.Clear();
                    tbClassTab.Clear();
                    tbStudentTab.Clear();
                    tbSubjectTab.Clear();

                    if (sheet != null)
                    {

                        // excute loop to get data from excel file by using NPOI references
                        for (int row = 1; row <= sheet.LastRowNum; row++)
                        {

                            if (sheet.GetRow(row) != null)
                            {
                                string sSTT = string.Empty;
                                if (sheet.GetRow(row).GetCell(0) != null)
                                {
                                    sheet.GetRow(row).GetCell(0).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(0).StringCellValue != null && sheet.GetRow(row).GetCell(0).StringCellValue.ToString() != string.Empty)
                                    {
                                        sSTT = sheet.GetRow(row).GetCell(0).StringCellValue;
                                    }
                                }
                                string sStudentCode = string.Empty;
                                if (sheet.GetRow(row).GetCell(1) != null)
                                {
                                    sheet.GetRow(row).GetCell(1).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(1).StringCellValue != null && sheet.GetRow(row).GetCell(1).StringCellValue.ToString() != string.Empty)
                                    {
                                        sStudentCode = sheet.GetRow(row).GetCell(1).StringCellValue;

                                    }
                                }
                                string sFistname = string.Empty;
                                if (sheet.GetRow(row).GetCell(2) != null)
                                {
                                    sheet.GetRow(row).GetCell(2).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(2).StringCellValue != null && sheet.GetRow(row).GetCell(2).StringCellValue.ToString() != string.Empty)
                                    {
                                        sFistname = sheet.GetRow(row).GetCell(2).StringCellValue;

                                    }
                                }
                                string sLastName = string.Empty;
                                if (sheet.GetRow(row).GetCell(3) != null)
                                {
                                    sheet.GetRow(row).GetCell(3).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(3).StringCellValue != null && sheet.GetRow(row).GetCell(3).StringCellValue.ToString() != string.Empty)
                                    {
                                        sLastName = sheet.GetRow(row).GetCell(3).StringCellValue;

                                    }
                                }
                                string sDayOfbird = string.Empty;
                                if (sheet.GetRow(row).GetCell(4) != null)
                                {
                                    sheet.GetRow(row).GetCell(4).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(4).StringCellValue != null && sheet.GetRow(row).GetCell(4).StringCellValue.ToString() != string.Empty)
                                    {
                                        sDayOfbird = sheet.GetRow(row).GetCell(4).StringCellValue;

                                    }
                                }
                                string sClass = string.Empty;
                                if (sheet.GetRow(row).GetCell(5) != null)
                                {
                                    sheet.GetRow(row).GetCell(5).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(5).StringCellValue != null && sheet.GetRow(row).GetCell(5).StringCellValue.ToString() != string.Empty)
                                    {
                                        sClass = sheet.GetRow(row).GetCell(5).StringCellValue;

                                    }
                                }

                                string sSubjectcode = string.Empty;
                                if (sheet.GetRow(row).GetCell(6) != null)
                                {
                                    if (sheet.GetRow(row).GetCell(6).StringCellValue != null && sheet.GetRow(row).GetCell(6).StringCellValue.ToString() != string.Empty)
                                    {
                                        sSubjectcode = sheet.GetRow(row).GetCell(6).StringCellValue;

                                    }
                                }

                                string sSubjectName = string.Empty;
                                if (sheet.GetRow(row).GetCell(7) != null)
                                {
                                    if (sheet.GetRow(row).GetCell(7).StringCellValue != null && sheet.GetRow(row).GetCell(7).StringCellValue.ToString() != string.Empty)
                                    {
                                        sSubjectName = sheet.GetRow(row).GetCell(7).StringCellValue;

                                    }
                                }
                                string sattendance = string.Empty;
                                if (sheet.GetRow(row).GetCell(8) != null)
                                {
                                    sheet.GetRow(row).GetCell(8).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(8).StringCellValue != null && sheet.GetRow(row).GetCell(8).StringCellValue != null)
                                    {
                                        sattendance = sheet.GetRow(row).GetCell(8).StringCellValue;

                                    }
                                }

                                string smidterm = string.Empty;
                                if (sheet.GetRow(row).GetCell(9) != null)
                                {
                                    sheet.GetRow(row).GetCell(9).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(9).StringCellValue != null && sheet.GetRow(row).GetCell(9).ToString() != string.Empty)
                                    {
                                        smidterm = sheet.GetRow(row).GetCell(9).StringCellValue;

                                    }
                                }
                                string sFistScore = string.Empty;
                                if (sheet.GetRow(row).GetCell(10) != null)
                                {
                                    sheet.GetRow(row).GetCell(10).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(10).StringCellValue != null && sheet.GetRow(row).GetCell(10).StringCellValue.ToString() != string.Empty)
                                    {
                                        sFistScore = sheet.GetRow(row).GetCell(10).StringCellValue;

                                    }
                                }
                                string sSecondScore = string.Empty;
                                if (sheet.GetRow(row).GetCell(11) != null)
                                {
                                    sheet.GetRow(row).GetCell(11).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(11).StringCellValue != null && sheet.GetRow(row).GetCell(11).StringCellValue.ToString() != string.Empty)
                                    {
                                        sSecondScore = sheet.GetRow(row).GetCell(11).StringCellValue;

                                    }
                                }
                                string sFinalScore = string.Empty;
                                if (sheet.GetRow(row).GetCell(12) != null)
                                {
                                    sheet.GetRow(row).GetCell(12).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(12).StringCellValue != null && sheet.GetRow(row).GetCell(12).StringCellValue.ToString() != string.Empty)
                                    {
                                        sFinalScore = sheet.GetRow(row).GetCell(12).StringCellValue;

                                    }
                                }
                                string sPerofattendance = string.Empty;
                                if (sheet.GetRow(row).GetCell(13) != null)
                                {
                                    sheet.GetRow(row).GetCell(13).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(13).StringCellValue != null && sheet.GetRow(row).GetCell(13).StringCellValue.ToString() != string.Empty)
                                    {
                                        sPerofattendance = sheet.GetRow(row).GetCell(13).StringCellValue;

                                    }
                                }

                                string sPerofMidterm = string.Empty;
                                if (sheet.GetRow(row).GetCell(14) != null)
                                {
                                    sheet.GetRow(row).GetCell(14).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(14).StringCellValue != null && sheet.GetRow(row).GetCell(14).StringCellValue.ToString() != string.Empty)
                                    {
                                        sPerofMidterm = sheet.GetRow(row).GetCell(14).StringCellValue;

                                    }

                                }
                                string sPerOFFinal = string.Empty;
                                if (sheet.GetRow(row).GetCell(15) != null)
                                {
                                    sheet.GetRow(row).GetCell(15).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(15).StringCellValue != null && sheet.GetRow(row).GetCell(15).StringCellValue.ToString() != string.Empty)
                                    {
                                        sPerOFFinal = sheet.GetRow(row).GetCell(15).StringCellValue;

                                    }
                                }

                                DataRow drcheck = tbdataexcel.NewRow();
                                //Add Datarow for Excel datatable.
                                drcheck["Note"] = string.Empty;
                                drcheck["IDStudentInSubject"] = -1;
                                drcheck["IDStudent"] = -1;
                                drcheck["IDClass"] = -1;
                                drcheck["IDSubject"] = -1;
                                drcheck["STT"] = sSTT;
                                drcheck["StudentCode"] = sStudentCode;
                                drcheck["Fistname"] = sFistname;
                                drcheck["LastName"] = sLastName;
                                drcheck["DayOfbird"] = sDayOfbird;
                                drcheck["Class"] = sClass;
                                drcheck["Subjectcode"] = sSubjectcode;
                                drcheck["SubjectName"] = sSubjectName;
                                drcheck["attendance"] = sattendance;
                                drcheck["midterm"] = smidterm;
                                drcheck["FistScore"] = sFistScore;
                                drcheck["SecondScore"] = sSecondScore;
                                drcheck["FinalScore"] = sFinalScore;
                                drcheck["Perofattendance"] = sPerofattendance;
                                drcheck["PerofMidterm"] = sPerofMidterm;
                                drcheck["PerOFFinal"] = sPerOFFinal;

                                if (sStudentCode != string.Empty && sClass != string.Empty && sSubjectcode != string.Empty && sFistname != string.Empty && sLastName != string.Empty && sSubjectName != string.Empty && sFinalScore != string.Empty)
                                {
                                    //Assign values into datarow on table StudentTAB
                                    int iStudentID = -1, iSubjectID = -1, iClassID = -1, iSectorID = -1;
                                    // Select query on datatable for comparing with database Student.
                                    string expression = "StudentCode = '" + sStudentCode + "'";
                                    DataRow[] foundRowsStudent;
                                    foundRowsStudent = tbstudent.Select(expression);
                                    if (foundRowsStudent.Length > 0)
                                    {
                                        iStudentID = Convert.ToInt32(foundRowsStudent[0]["ID"]);

                                    }

                                    string expression1 = "Code = '" + sClass + "'";

                                    DataRow[] foundRowsClass;
                                    foundRowsClass = tbClass.Select(expression1);

                                    if (foundRowsClass.Length > 0)
                                    {

                                        iClassID = Convert.ToInt32(foundRowsClass[0]["ID"]);
                                        iSectorID = Convert.ToInt32(foundRowsClass[0]["SectorID"]);
                                    }

                                    string expression2 = "Code = '" + sSubjectcode + "'";
                                    DataRow[] foundRowsSubject;
                                    foundRowsSubject = tbsubject.Select(expression2);


                                    if (foundRowsSubject.Length > 0)
                                    {

                                        iSubjectID = Convert.ToInt32(foundRowsSubject[0]["ID"]);

                                    }

                                    string sSlectStudentClass = "StudentCode = '" + sStudentCode + "'";
                                    DataRow[] drRowStudentClass = tbStudentTab.Select(sSlectStudentClass);

                                    if (drRowStudentClass.Length == 0)
                                    {
                                        DataRow drstudent = tbStudentTab.NewRow();
                                        if (iStudentID == -1)
                                        {
                                            drstudent["Check"] = true;
                                            drstudent["Note"] = string.Empty;

                                        }
                                        else
                                        {
                                            drstudent["Check"] = false;
                                            drstudent["Note"] = "Sinh Viên Đã Tồn Tại";
                                        }

                                        drstudent["StudentCode"] = sStudentCode;
                                        drstudent["ID"] = iStudentID;
                                        drstudent["SectorID"] = iSectorID;
                                        drstudent["ClassID"] = iClassID;
                                        drstudent["ClassCode"] = sClass;
                                        drstudent["Fistname"] = sFistname;
                                        drstudent["LastName"] = sLastName;
                                        tbStudentTab.Rows.Add(drstudent);
                                    }
                                    //Assign values into datarow on table ClassTab
                                    string sSlectClass = "Class = '" + sClass + "'";

                                    DataRow[] drRowClass = tbClassTab.Select(sSlectClass);

                                    if (drRowClass.Length == 0)
                                    {
                                        DataRow drClass = tbClassTab.NewRow();

                                        if (iClassID == -1)
                                        {
                                            drClass["Check"] = true;
                                            drClass["Note"] = string.Empty;
                                        }
                                        else
                                        {
                                            drClass["Check"] = false;
                                            drClass["Note"] = "Lớp đã tồn tại";
                                        }
                                        drClass["SectorID"] = iSectorID;
                                        drClass["ID"] = iClassID;
                                        drClass["Class"] = sClass;
                                        tbClassTab.Rows.Add(drClass);
                                    }

                                    string sSubjectClass = "Subjectcode = '" + sSubjectcode + "'";
                                    DataRow[] drSubjectClass;
                                    drSubjectClass = tbSubjectTab.Select(sSubjectClass);
                                    if (drSubjectClass.Length == 0)
                                    {
                                        DataRow drSubject = tbSubjectTab.NewRow();
                                        if (iSubjectID == -1)
                                        {
                                            drSubject["Check"] = true;
                                            drSubject["Note"] = string.Empty;
                                        }
                                        else
                                        {
                                            drSubject["Check"] = false;
                                            drSubject["Note"] = "Môn học đã tồn tại";
                                        }

                                        drSubject["ID"] = iSubjectID;
                                        drSubject["Subjectcode"] = sSubjectcode;
                                        drSubject["SubjectName"] = sSubjectName;
                                        tbSubjectTab.Rows.Add(drSubject);
                                    }
                                    drcheck["IDStudent"] = iStudentID;
                                    drcheck["IDClass"] = iClassID;
                                    drcheck["IDSubject"] = iSubjectID;

                                    if (iStudentID != -1 && iClassID != -1 && iSubjectID != -1)
                                    {
                                        string expression3 = "StudentID = '" + iStudentID + "' AND ClassID = '" + Convert.ToInt32(iClassID) + "' AND SubjectID = '" + Convert.ToInt32(iSubjectID) + "' ";
                                        DataRow[] foundRowsSCore;
                                        foundRowsSCore = tbScore.Select(expression3);
                                        if (foundRowsSCore.Length > 0)
                                        {
                                            drcheck["Check"] = false;
                                            drcheck["Note"] = "Sinh viên đã có điểm";
                                        }
                                        else
                                        {
                                            drcheck["Check"] = true;
                                            drcheck["Note"] = string.Empty;
                                        }
                                    }
                                    else if (iStudentID == -1)
                                    {
                                        drcheck["Check"] = false;
                                        drcheck["Note"] = "Sinh viên không tồn tại";
                                    }
                                    else if (iClassID == -1)
                                    {
                                        drcheck["Check"] = false;
                                        drcheck["Note"] = "Lớp không tồn tại";
                                    }
                                    else if (iSubjectID == -1)
                                    {
                                        drcheck["Check"] = false;
                                        drcheck["Note"] = "Môn học không tồn tại";
                                    }

                                }
                                else
                                {
                                    drcheck["Check"] = false;
                                    drcheck["Note"] = "Thiếu thông tin";

                                }
                                tbdataexcel.Rows.Add(drcheck);
                                // add data row into datatables. 

                            }

                        }
                        grdimportdata.DataSource = tbdataexcel;

                    }
                }

            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }
        private void btnImport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (MessageBox.Show("Bạn có chắc chắn muốn chuyển dữ liệu vào hệ thống không ?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                try
                {

                    Cnn.Open();
                    if (tbdataexcel == null || tbdataexcel.Rows.Count == 0 || gridView1.RowCount == 0)
                    {
                        MessageBox.Show("Không có dữ liệu để chuyển dữ liệu vào hệ thống, Vui lòng kiểm tra lại");
                    }
                    else
                    {

                        DataRow[] _RowFilter;
                        // To declare new datatable to include data has filtered rows has values equal "true" from datatable Excle
                        string sExpressionCheck = "Check ='true'";
                        _RowFilter = tbdataexcel.Select(sExpressionCheck);

                        if (_RowFilter.Length == 0)
                        {
                            MessageBox.Show("Dữ liệu không hợp lệ,Vui lòng kiểm tra lại", "Thông báo", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);

                        }
                        else
                        {
                            // System.Diagnostics.Trace.WriteLine(DateTime.Now);

                            // Insert data into table StudentInSemeter
                            for (int irowSemester = 0; irowSemester < tbStudentTab.Rows.Count; irowSemester++)
                            {

                                SqlCommand cmmInsertStundentInSemester = new SqlCommand("Insert into  PRI_StudentInSemester ( YearID, SemesterID, DepartmentID, StudentID, ClassID,  SchoolFee, SchoolFeeDebt, ResitFeeDebt, TotalMoneyDebt, StatusPrint, CreatedBy, CreatedDate) " + " VALUES ( @YearID, @SemesterID, @DepartmentID, @StudentID, @ClassID, @SchoolFee, @SchoolFeeDebt, @ResitFeeDebt, @TotalMoneyDebt, @StatusPrint, @CreatedBy, @CreatedDate) ", Cnn);
                                cmmInsertStundentInSemester.Parameters.Add("@YearID", SqlDbType.Int).Value = iYearschool;
                                cmmInsertStundentInSemester.Parameters.Add("@SemesterID", SqlDbType.Int).Value = iSemesterID;
                                cmmInsertStundentInSemester.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = iDepart;
                                cmmInsertStundentInSemester.Parameters.Add("@StudentID", SqlDbType.Int).Value = tbStudentTab.Rows[irowSemester]["ID"]; ;
                                cmmInsertStundentInSemester.Parameters.Add("@ClassID", SqlDbType.Int).Value = tbStudentTab.Rows[irowSemester]["ClassID"];
                                cmmInsertStundentInSemester.Parameters.Add("@SchoolFee", SqlDbType.Money).Value = 0;
                                cmmInsertStundentInSemester.Parameters.Add("@SchoolFeeDebt", SqlDbType.Money).Value = 0;
                                cmmInsertStundentInSemester.Parameters.Add("@ResitFeeDebt", SqlDbType.Money).Value = 0;
                                cmmInsertStundentInSemester.Parameters.Add("@TotalMoneyDebt", SqlDbType.Money).Value = 0;
                                cmmInsertStundentInSemester.Parameters.Add("@StatusPrint", SqlDbType.Bit).Value = 0;
                                cmmInsertStundentInSemester.Parameters.Add("@CreatedBy", SqlDbType.NVarChar).Value = UserName;
                                cmmInsertStundentInSemester.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                                cmmInsertStundentInSemester.ExecuteNonQuery();
                            }

                            for (int irow = 0; irow < _RowFilter.Length; irow++)
                            {
                                SqlCommand cmmInsertStundentInSubject = new SqlCommand("Insert into  PRI_StudentInSubject (YearID, SemesterID, DepartmentID, StudentID, SubjectID, ClassID, AttendanceScore, MidtermScore, FinalExamScore1, FinalExcamScore2, TotalScore, AttendanceScorePercent , MidtermScorePercent, CreatedBy, CreatedDate) " + " VALUES (@YearID, @SemesterID, @DepartmentID, @StudentID, @SubjectID, @ClassID, @AttendanceScore, @MidtermScore, @FinalExamScore1, @FinalExcamScore2, @TotalScore,@AttendanceScorePercent,@MidtermScorePercent, @CreatedBy, @CreatedDate) ", Cnn);
                                // add values for Paremeter
                                cmmInsertStundentInSubject.Parameters.Add("@YearID", SqlDbType.Int).Value = iYearschool;
                                cmmInsertStundentInSubject.Parameters.Add("@SemesterID", SqlDbType.Int).Value = iSemesterID;
                                cmmInsertStundentInSubject.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = iDepart;
                                cmmInsertStundentInSubject.Parameters.Add("@StudentID", SqlDbType.Int).Value = _RowFilter[irow]["IDStudent"];
                                cmmInsertStundentInSubject.Parameters.Add("@SubjectID", SqlDbType.Int).Value = _RowFilter[irow]["IDSubject"];
                                cmmInsertStundentInSubject.Parameters.Add("@ClassID", SqlDbType.Int).Value = _RowFilter[irow]["IDClass"];
                                cmmInsertStundentInSubject.Parameters.Add("@AttendanceScore", SqlDbType.NVarChar).Value = _RowFilter[irow]["attendance"];
                                cmmInsertStundentInSubject.Parameters.Add("@MidtermScore", SqlDbType.NVarChar).Value = _RowFilter[irow]["midterm"];
                                cmmInsertStundentInSubject.Parameters.Add("@FinalExamScore1", SqlDbType.NVarChar).Value = _RowFilter[irow]["FistScore"];
                                cmmInsertStundentInSubject.Parameters.Add("@FinalExcamScore2", SqlDbType.NVarChar).Value = _RowFilter[irow]["SecondScore"];
                                cmmInsertStundentInSubject.Parameters.Add("@TotalScore", SqlDbType.NVarChar).Value = _RowFilter[irow]["FinalScore"];
                                cmmInsertStundentInSubject.Parameters.Add("@AttendanceScorePercent", SqlDbType.NVarChar).Value = _RowFilter[irow]["Perofattendance"];
                                cmmInsertStundentInSubject.Parameters.Add("@MidtermScorePercent", SqlDbType.NVarChar).Value = _RowFilter[irow]["PerofMidterm"];
                                cmmInsertStundentInSubject.Parameters.Add("@CreatedBy", SqlDbType.NVarChar).Value = UserName;
                                cmmInsertStundentInSubject.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                                cmmInsertStundentInSubject.ExecuteNonQuery();
                            }

                            SqlCommand cmmSubjectSetting = new SqlCommand();
                            cmmSubjectSetting.Connection = Cnn;
                            cmmSubjectSetting.CommandType = CommandType.StoredProcedure;
                            cmmSubjectSetting.CommandText = "sp_SubjectSetting";
                            cmmSubjectSetting.Parameters.Add("@p_DepartID", SqlDbType.Int).Value = iDepart;
                            cmmSubjectSetting.Parameters.Add("@p_YearID", SqlDbType.Int).Value = iYearschool;
                            cmmSubjectSetting.Parameters.Add("@p_Semester", SqlDbType.Int).Value = iSemesterID;
                            cmmSubjectSetting.Parameters.Add("@p_CreateBy", SqlDbType.NVarChar).Value = UserName;
                            cmmSubjectSetting.ExecuteNonQuery();
                            //  System.Diagnostics.Trace.WriteLine(DateTime.Now);
                            MessageBox.Show("Chuyển dữ liệu vào hệ thống thành công ");
                            this.DialogResult = DialogResult.OK;
                            this.Close();
                            Cnn.Close();
                        }
                    }

                }

                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);

                }
                finally
                {
                    Cnn.Close();
                }
            }

        }
        private void btnCheckData_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            FrmCheckNewDataInexcel checkdata = new FrmCheckNewDataInexcel();

            try
            {
                if (tbStudentTab.Rows.Count == 0 || tbClassTab.Rows.Count == 0 || tbSubjectTab.Rows.Count == 0)
                {
                    MessageBox.Show("Không có dữ liệu", "Cảnh báo", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);

                }
                else
                {

                    checkdata.tbStudentCode = tbStudentTab;
                    checkdata.tbClass = tbClassTab;
                    checkdata.tbSubject = tbSubjectTab;
                    checkdata.tbExcel = tbdataexcel;
                    checkdata.tbScore = tbScore;
                    checkdata.iDepartmentID = iDepart;
                    checkdata.iYearID = iYearschool;
                    checkdata.iSemesterID = iSemesterID;
                    checkdata.ShowDialog();
                    grdimportdata.DataSource = tbdataexcel;

                }
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }

        }
        private void btnCancle_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (MessageBox.Show("Thoát giao diện này?", "Thông báo", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
            {
                this.Close();
            }
        }

        private void gridView1_RowStyle(object sender, DevExpress.XtraGrid.Views.Grid.RowStyleEventArgs e)
        {
            GridView View = sender as GridView;
            if (e.RowHandle >= 0)
            {
                string sCheck = Convert.ToString(View.GetRowCellDisplayText(e.RowHandle, View.Columns["Note"]));
                if (sCheck == "Thiếu thông tin")
                {
                    e.Appearance.BackColor = Color.Salmon;

                }
                if (sCheck == "Sinh viên đã có điểm")
                {
                    e.Appearance.BackColor = Color.Goldenrod;
                }
                if (sCheck == "Sinh viên không tồn tại" || sCheck == "Lớp không tồn tại" || sCheck == "Môn học không tồn tại")
                {
                    e.Appearance.BackColor = Color.PaleGreen;
                }

            }

        }

        #region Primary Members


        #endregion
    }
}